In [1]:
import seaborn as sns
import numpy as np
import pandas as pd
import time
from pprint import pprint
from sqlalchemy import create_engine
import datashader as ds
import matplotlib.pyplot as plt
from colorcet import fire, blues
from datashader.utils import export_image
import seaborn as sns
from datashader import transfer_functions as tf
In [2]:
POSTGRES_USERNAME = "sahil"
POSTGRES_PASSWORD = "zxcvbnm"
POSTGRES_DBNAME = "postgres"
POSTGRES_DB_ADDRESS = "localhost"
url = 'postgresql://{}:{}@{}:{}/{}'.format(POSTGRES_USERNAME, POSTGRES_PASSWORD, POSTGRES_DB_ADDRESS, 5432, POSTGRES_DBNAME)
engine = create_engine(url)
In [3]:
#select pdpi of 2016-2017
pdpi_tables = pd.read_sql_query("select tablename from pg_tables where tablename ~* 'pdpi_(2016|2017).*' order by tablename asc;"
, con=engine)
pdpi_tables.head()
Out[3]:
In [4]:
addr_index = pd.read_sql_table("addr_index", engine)
addr_index.head()
Out[4]:
In [5]:
def remove_whitespace(x):
"""
Helper function to remove any blank space from a string
x: a string
"""
try:
# Remove spaces inside of the string
x = "".join(x.split())
except:
pass
return x
In [6]:
addr_index["postcode"] = addr_index["postcode"].apply(remove_whitespace)
addr_index.head()
Out[6]:
In [7]:
post_code_pop = pd.read_sql_table("postcodes_population", engine)
post_code_pop.head()
Out[7]:
In [8]:
merged_df = pd.merge(addr_index, post_code_pop[["postcode", "county"]], how="left", on="postcode")
In [9]:
merged_df.head()
Out[9]:
In [ ]:
# dont rerun this cell
# table was already created
merged_df.to_sql("addr_index2", engine)
In [10]:
addr_index2 = merged_df
del merged_df
addr_index2.head()
Out[10]:
In [11]:
import geopandas as gpd
# boundry_shape_file = gpd.read_file("/home/sahil/Documents/code/masters_thesis/Prescribing Data/british_counties_epsg_4326.shp")
boundry_shape_file = gpd.read_file("/home/sahil/Documents/code/masters_thesis/Prescribing Data/Boundary-line-ceremonial-counties_region.shp")
boundry_shape_file.shape
boundry_shape_file.plot(figsize=(20,10))
Out[11]:
In [12]:
boundry_shape_file["Name"] = boundry_shape_file["Name"].str.lower().apply(remove_whitespace)
boundry_shape_file.head()
Out[12]:
In [13]:
boundry_shape_file.plot(figsize=(20,10))
Out[13]:
In [19]:
# now we do a point in polygon analysis
# select specfic section 03 chapter 01
drug_code = "drug_0309"
frames = []
for t in pdpi_tables.tablename:
query = "select practice from " + str(t) + " where chapter like '03' and section like '09';"
print(query)
start = time.time()
df = pd.read_sql_query(query, con=engine)
df["period"] = t.split("_")[-1]
frames.append(df)
print(time.time()-start)
# merged the practice with its county
merged_df = pd.merge(df, addr_index2, how="left", on="practice")
merged_df.head()
# get the county counts
county_counts = merged_df["county"].value_counts()
county_counts = pd.DataFrame(county_counts)
county_counts = county_counts.reset_index()
county_counts.columns = ["Name", "freq_of_occurance"]
county_counts["Name"] = county_counts["Name"].str.lower().apply(remove_whitespace)
county_counts["period"] = t.split("_")[-1]
print(county_counts.head())
plt.figure()
sns.distplot(county_counts["freq_of_occurance"])
plt.savefig("./hist/" + drug_code + t.split("_")[-1] + "_hist" + ".png")
# # boundry_shape_file.merge with county
merged_boundry = boundry_shape_file.merge(county_counts, on='Name', how="left")
print(merged_boundry.shape)
frames.append(county_counts)
# #fill nan with zero - county which have no occurances
merged_boundry = merged_boundry.fillna(0)
merged_boundry2 = merged_boundry[merged_boundry["Name"] != "lancashire"]
# #chlorpleth map
# #k = number of classes
plt.figure()
merged_boundry2.plot(column='freq_of_occurance',
k=8, # number of classes
cmap='viridis',
legend=True,
scheme='fisher_jenks',
figsize=(20,20),
linewidth=2.0)
plt.tight_layout()
plt.title("Chlorpleth Map of " + drug_code + " for " + t.split("_")[-1])
plt.savefig("./choropleth_without/" + t.split("_")[-1] + "_without_lancashire.png", bbox_inches='tight', pad_inches=0.2)
In [21]:
import imageio
import glob
images = []
filenames = sorted(glob.glob("./choropleth_without/*.png") )
print(filenames)
for filename in filenames:
images.append(imageio.imread(filename))
imageio.mimsave('choropleth_timelapse.gif', images)
# \end{lstlisting}
In [22]:
from IPython.display import HTML
HTML('<img src="choropleth_timelapse.gif">')
Out[22]:
